package com.gdssoft.pdaweb.service.am.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.gdssoft.core.command.QueryFilter;
import com.gdssoft.core.service.impl.BaseServiceImpl;
import com.gdssoft.core.util.ContextUtil;
import com.gdssoft.core.util.DateUtil;
import com.gdssoft.pdaweb.dao.am.PdaAmMeterListDao;
import com.gdssoft.pdaweb.model.am.PdaAmMeter;
import com.gdssoft.pdaweb.model.am.PdaAmMeterList;
import com.gdssoft.pdaweb.model.water.PdaWaterList;
import com.gdssoft.pdaweb.service.CrmService;
import com.gdssoft.pdaweb.service.am.PdaAmMeterListService;
import com.gdssoft.pdaweb.service.am.PdaAmMeterService;
import com.ibm.icu.util.Calendar;

public class PdaAmMeterListServiceImpl extends BaseServiceImpl<PdaAmMeterList> implements PdaAmMeterListService{
	private PdaAmMeterListDao dao;
	@Resource
	private PdaAmMeterService pdaAmMeterService;
	
	@Resource
	private CrmService crmService;
	
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
	
	public PdaAmMeterListServiceImpl(PdaAmMeterListDao dao) {
		super(dao);
		this.dao=dao;
	}

	@Override
	public List<PdaAmMeterList> checkXLSFormat(String importMonth,File file) {

        System.out.println("checkXLSFormat's importMonth is "+importMonth);
		System.out.println("checkXLSFormat start");
		
		List<PdaAmMeterList> errorlist = new ArrayList<PdaAmMeterList>();		
		boolean fileTypeError = false;
		// 通过文件流过去要操作Excel的句柄
		FileInputStream fi;
		Workbook wb = null;
		try {
			fi = new FileInputStream(file);
			wb = new HSSFWorkbook(fi);
		} catch (Exception e1) {
			System.out.println("文件类型错误");
			logger.error("文件类型错误", e1);
			fileTypeError = true;
		}

		if(true == fileTypeError){
			PdaAmMeterList pl = new PdaAmMeterList();
			pl.setAmId("");
			pl.setRemark("文件类型错误");
			errorlist.add(pl);
		}else{
			try {
				// 获取Excel实例的第一页
				Sheet sheet = wb.getSheetAt(0);
				int rowNum = sheet.getLastRowNum()+1;
				String amId = "";
				long degree = 0;
				for (int i = 1; i < rowNum; i++) {
					 Row row = sheet.getRow(i);
					 //表的编号
					 Cell cell = row.getCell(0);
					 amId = cell.getStringCellValue();
					 //读数
					 cell = row.getCell(2);
					try {
						degree = Long.parseLong(cell.getStringCellValue().trim());
					} catch (NumberFormatException e) {
						try {
							degree = (long) cell.getNumericCellValue(); // WARNING:double->long
						} catch (Exception e2) {
							// 电表读数格式有误
							PdaAmMeterList pl = new PdaAmMeterList();
							pl.setAmId(amId);
							pl.setRemark("电表读数格式有误");
							errorlist.add(pl);
						}
					}
					//检查水表编号是否存在
					if(false == checkAmidExist(amId)){	
						PdaAmMeterList pl = new PdaAmMeterList();
						pl.setAmId(amId);
						pl.setRemark("电表编号不存在");
						errorlist.add(pl);
					}
					//该表当月记录已经存在
					if(true == recodExist(amId,importMonth)){
						PdaAmMeterList pl = new PdaAmMeterList();
						pl.setAmId(amId);
						pl.setRemark("该表当月记录已经存在，请不要重复导入。");
						errorlist.add(pl);
					}					
				}
			} catch (Exception e) {
				System.out.println("获取失败");
				logger.error("获取失败", e);
				PdaAmMeterList pl = new PdaAmMeterList();
				pl.setAmId("");
				pl.setRemark("读取文件失败，请检查重试！");
				errorlist.add(pl);
			}
		}
		return errorlist;
	}


	@Override
	public int saveExcel(String importMonth,File file) {
		
        System.out.println("saveExcel's importMonth is "+importMonth);
		System.out.println("saveExcel start");
		int count = 0;
		// 通过文件流过去要操作Excel的句柄
		FileInputStream fi;
		Workbook wb = null;
		try {
			fi = new FileInputStream(file);
			wb = new HSSFWorkbook(fi);
		} catch (Exception e1) {
			System.out.println("文件类型错误");
			logger.error("文件类型错误", e1);
		}
		String username = ContextUtil.getCurrentUser().getUsername();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			// 获取Excel实例的第一页
			Sheet sheet = wb.getSheetAt(0);
			int rowNum = sheet.getLastRowNum() + 1;
			long degree = 0;
			for (int i = 1; i < rowNum; i++) {
				PdaAmMeterList pdaAmMeterList = new PdaAmMeterList();
				
				Row row = sheet.getRow(i);
				//对应的excel表的内容填充到pdaAmMeterList对象中
				//amId
				Cell cell = row.getCell(0);
				pdaAmMeterList.setAmId(cell.getStringCellValue());
				//读数
				cell = row.getCell(2);
				try{
					pdaAmMeterList.setDegree(Long.parseLong(cell.getStringCellValue().trim()));
				}catch (NumberFormatException e) {
					pdaAmMeterList.setDegree((long)cell.getNumericCellValue());
				}
				//记录者				
				pdaAmMeterList.setRecorderId(username);
				//记录时间
				pdaAmMeterList.setRecordtime(new Date());
				//月份
				pdaAmMeterList.setMonth(importMonth);
				//备注				
				pdaAmMeterList.setRemark("");
				//修改者
				pdaAmMeterList.setUpdataid(username);
				//修改日期
				pdaAmMeterList.setUpdatedate(sdf.format(new Date()));
				//创建时间
				pdaAmMeterList.setCreateddt(sdf.format(new Date()));
				//创建者
				pdaAmMeterList.setCreatedby(username);
				//最后编辑时间
				pdaAmMeterList.setLasteditdt(sdf.format(new Date()));
				//最后编辑者
				pdaAmMeterList.setLasteditby(username);
				
				//检查是否异常
				String str = checkNomal(pdaAmMeterList.getAmId() , pdaAmMeterList.getDegree() , pdaAmMeterList.getMonth());
				pdaAmMeterList.setStatus(str);
				
				save(pdaAmMeterList);
				count++;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("获取失败");
			logger.error("获取失败", e);
		}
		
		return count;
	}
	
	@Override
	public void excelExport(HttpServletResponse response,PdaAmMeterList pdaAmMeterList,String user){
		
		List<PdaAmMeterList> detailList = exportList(pdaAmMeterList);	//生成导出数据
		SimpleDateFormat sdf0 = new SimpleDateFormat("yyyy-MM-dd");
		String yearMonth = pdaAmMeterList.getMonth();
		try {
			yearMonth = sdf0.format(sdf0.parse(yearMonth+"-00"));
		} catch (ParseException e1) {
			logger.error("日期转换yyyy-MM出错！");
			try {
				yearMonth = sdf.format(sdf.parse(yearMonth));
			} catch (ParseException e) {
				logger.error("日期转换yyyy-MM-dd出错！");
			}
		}
				
		response.setCharacterEncoding("UTF-8");
		response.setContentType("application/msexcel");
		response.setHeader("Content-Disposition",
				"attachment; filename=AM_Meter_List("+yearMonth+").xls");
		OutputStream output = null;
		WritableWorkbook wwb =null;
		WritableSheet ws=null;
		WritableFont font1=new WritableFont(WritableFont.createFont("楷体 _GB2312"),10,WritableFont.NO_BOLD );
		WritableFont font2=new WritableFont(WritableFont.createFont("楷体 _GB2312"),10,WritableFont.NO_BOLD );
		WritableFont fontHead=new WritableFont(WritableFont.createFont("楷体 _GB2312"),18,WritableFont.BOLD );
		WritableFont fontTitle=new WritableFont(WritableFont.createFont("楷体 _GB2312"),10,WritableFont.NO_BOLD );
		WritableCellFormat format1=new WritableCellFormat(font1);
		WritableCellFormat format2=new WritableCellFormat(font2);
		WritableCellFormat formatHead=new WritableCellFormat(fontHead);
		WritableCellFormat formatTitle=new WritableCellFormat(fontTitle);
		
		try {
			format1.setBorder(Border.ALL, BorderLineStyle.THIN);
			format2.setBorder(Border.NONE, null);
			formatHead.setBorder(Border.ALL, BorderLineStyle.THIN);
			formatTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
			formatHead.setAlignment(jxl.format.Alignment.CENTRE);
			formatHead.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			output = response.getOutputStream();
			wwb = jxl.Workbook.createWorkbook(output);
			ws = wwb.createSheet("sheet1", 0);
			//ws.addCell(new Label(0, 0, "XX公司("+yearMonth+")XX报表",formatHead));		
			ws.addCell(new Label(0, 0, "电表号",format1));
			ws.addCell(new Label(1, 0, "上月读数",format1));
			ws.addCell(new Label(2, 0, "录入本月读数",format1));
			ws.addCell(new Label(3, 0, "区",format1));
			ws.addCell(new Label(4, 0, "栋",format1));
			ws.addCell(new Label(5, 0, "房",format1));
			
			for(int i=0;i<detailList.size();i++){
				ws.setRowView(i+1, 300);// 设置行高
				PdaAmMeterList pl = detailList.get(i);
				for(int j=0;j<6;j++){
					switch(j){
						case 0:{
					        ws.addCell(new Label(j,i+1,pl.getAmId(),format1));
					        ws.setColumnView(j, 16);// 设置列宽
					        break;}
						case 1:{ 	//上月读数
					        ws.addCell(new Label(j,i+1,pl.getLastDegree()==-1?"":pl.getLastDegree().toString(),format1));
					        ws.setColumnView(j, 16);// 设置列宽
					        break;}
						case 2:{
					        ws.addCell(new Label(j,i+1,"",format1));	//置空
					        ws.setColumnView(j, 16);// 设置列宽
					        break;}
						case 3: {	//区
					        ws.addCell(new Label(j,i+1,pl.getPdaAmMeter().getDistrictId(),format1));
					        ws.setColumnView(j, 8);// 设置列宽
					        break;}
						case 4: {
					        ws.addCell(new Label(j,i+1,pl.getPdaAmMeter().getBuildingId(),format1));
					        ws.setColumnView(j, 8);// 设置列宽
					        break;}
						case 5: { 
							ws.addCell(new Label(j,i+1,pl.getPdaAmMeter().getAparmentName(),format1));
					        ws.setColumnView(j, 30);// 设置列宽
					        break;}
					}
				}
			}
			
//			ws.addCell(new Label(0,detailList.size()+3,"制表："+user,format2));
//			ws.addCell(new Label(0,detailList.size()+4,"日期："+sdf.format(new Date()),format2));
//			ws.addCell(new Label(3,detailList.size()+3,"审核：",format2));
//			ws.addCell(new Label(3,detailList.size()+4,"日期：",format2));
						
//			ws.mergeCells(0, 0, 5, 0);//合并单元格	
//	        ws.setRowView(0, 700);// 设置行高
	        ws.setRowView(0, 400);// 表头：设置行高
	        
	        wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
		
	/**
	 * 生成要导出的数据
	 * @author F3229233 2013-1-15 下午2:55:36 
	 * @param pl
	 * @return
	 */
	private List<PdaAmMeterList> exportList(PdaAmMeterList pl) {
		List<PdaAmMeterList> list = new ArrayList<PdaAmMeterList>();
		
		//list = this.query(pl);	//TODO:该list是用以测试,实际带出的list是根据PDA_AM_METER生成的
		
		//则生成当月list数据
		List<PdaAmMeter> pms = pdaAmMeterService.getAllForExcel();
		String monthStr = pl.getMonth();
		String lastMonthStr = DateUtil.getLastMonthStr(monthStr);
		
		for(PdaAmMeter pm : pms){
			PdaAmMeterList newpl = new PdaAmMeterList();
			newpl.setPdaAmMeter(pm);
			newpl.setAmId(pm.getAmId());
			newpl.setLastDegree(this.getDegreeByTime(pm.getAmId(),lastMonthStr));
			newpl.setMonth(monthStr);
			list.add(newpl);
		}
		
		return list;
	}
	
	@Override
	public int queryCount(PdaAmMeterList pdaAmMeterList) {
		return dao.queryCount(pdaAmMeterList);
	}
	
	@Override
	public List<PdaAmMeterList> query(PdaAmMeterList pdaAmMeterList,int start,int to) {
		String lastMonth = DateUtil.getLastMonthStr(pdaAmMeterList.getMonth());
		List<PdaAmMeterList> list = dao.query(pdaAmMeterList,start,to);		
		for(PdaAmMeterList pl:list){
			pl.setLastDegree(this.getDegreeByTime(pl.getAmId(), lastMonth));
		}
		return list;
	}
	
	@Override
	public List<PdaAmMeterList> getList(QueryFilter filter, String month) {
		String lastMonth = DateUtil.getLastMonthStr(month);
		List<PdaAmMeterList> list = this.getAll(filter);
		for(PdaAmMeterList pl : list){
			Long lastDegree = this.getDegreeByTime(pl.getAmId(), lastMonth);
			pl.setLastDegree(lastDegree);
		}
		return list;
	}
	
    @Override
	public boolean checkAmidExist(String amId)
	{
		boolean ret = dao.checkAmidExist(amId);
		return ret;
	}
    
    @Override
    public boolean recodExist(String amId,String importMonth)
    {
    	boolean ret = dao.recodExist(amId,importMonth);
    	return ret;
    }
    
	@Override
    public String checkNomal(String amId , Long thisDegree,String month){
//		String status = "0";
//    	Date date = new Date();
//    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
//    	Calendar cd = Calendar.getInstance();
//    	try{ 
//    		date = sdf.parse(month); //将string类型转换为date类型
//    	}catch(Exception e){
//    		logger.error(this.getClass().getName()+".checkNomal()中日期格式转换错误！");
//    	}
//    	
//		cd.setTime(date);
//		cd.add(Calendar.MONTH, -1);//上个月日期
//		//上月读数
//    	Long lastMonthDegree = getDegreeByTime(amId,sdf.format(cd.getTime()));
//    	cd.add(Calendar.MONTH, +1);//恢复到本月日期
//    	
//    	long curMonthUsage = thisDegree - lastMonthDegree;
//    	
//    	if(curMonthUsage <= 0){
//    		status = "2";
//    	}else{
//    		if(isCurAdd(amId) > 0){
//        		status = "4";
//        	}else{
//        		cd.add(Calendar.MONTH, -2);//上2个月日期
//            	//上2个月读数
//            	Long lastTwoMonthDegree = getDegreeByTime(amId,sdf.format(cd.getTime()));
//            	cd.add(Calendar.MONTH, +2);//恢复到本月日期
//
//            	cd.add(Calendar.YEAR, -1);//去年同月日期
//            	//去年当月读数
//            	Long lastYearCurMonth = getDegreeByTime(amId,sdf.format(cd.getTime()));
//            	cd.add(Calendar.MONTH, -1);//去年上月日期
//                //去年上月读数
//            	Long lastYearLastMonth = getDegreeByTime(amId,sdf.format(cd.getTime()));
//
//            	//上月度数
//            	Long lastMonthUsage = lastMonthDegree-lastTwoMonthDegree;
//            	//去年同月度数
//            	Long lastYearCurMonthUsage = lastYearCurMonth-lastYearLastMonth;
//            	
//            	if(curMonthUsage > (lastMonthUsage+lastYearCurMonthUsage)*0.6){
//            		status = "3";
//            	}
//        	}
//    	}
//    	
//    	return status;
		String status = "0";
				String lastMonth = DateUtil.getLastMonthStr(month);
				//上月读数
				long lastMonthDegree = getDegreeByTime(amId,lastMonth);
		    	if(-1 == lastMonthDegree){
		    		lastMonthDegree = 0L;
		    	}
		    	//本月度数
		    	long curMonthUsage = thisDegree - lastMonthDegree;
		    	if(curMonthUsage < 0){
		    		status = "2";
		    	}else{
		    	    	//上2个月读数
		    	    	long lastTwoMonthDegree = getDegreeByTime(amId,DateUtil.getLastMonthStr(lastMonth));
		    	    	if(lastTwoMonthDegree < 0) lastTwoMonthDegree = 0L;
		    	    	
		    	    	Calendar cd = Calendar.getInstance();
		    	    	cd.setTime(DateUtil.dateParse(month, "yyyy-MM"));
		    	    	cd.add(Calendar.YEAR, -1);//去年同月日期
		    	    	String lastYearMonth = DateUtil.dateFormat(cd.getTime(), "yyyy-MM");
		    	    	
		    	    	//去年当月读数
		    	    	long lastYearCurMonth = getDegreeByTime(amId,lastYearMonth);
		    	    	if(lastYearCurMonth < 0) lastYearCurMonth = 0L;
		    	    	
		    	        //去年上月读数
		    	    	long lastYearLastMonth = getDegreeByTime(amId,DateUtil.getLastMonthStr(lastYearMonth));
		    	    	if(lastYearLastMonth < 0) lastYearLastMonth = 0L;
		    	    	
		    	    	//上月度数
		    	    	long lastMonthUsage = lastMonthDegree-lastTwoMonthDegree;
		    	    	//去年同月度数
		    	    	long lastYearCurMonthUsage = lastYearCurMonth-lastYearLastMonth;
		    	    	// 计算平均用量，作为判断的参考值
		    	    	long averageUsage = lastMonthUsage;
		    	    	if (lastYearCurMonthUsage > 0) {
		    	    		averageUsage = (lastMonthUsage + lastYearCurMonthUsage) / 2;
		    	    	}
		    	    	// 大于（上月与去年同月用量的平均数）10%，则为异常
		    	    	if(curMonthUsage > averageUsage*1.1){
		    	    		status = "3";
		    	    	}else{
		    	    		status = "0";
		    	    	}
		    	}
    	return status;
    }
    
    @Override
    public Long getDegreeByTime(String amId,String month)
    {
    	return dao.getDegreeByTime(amId,month);
    }
    

	@Override
	public boolean check(String ids, String status) {
		boolean result = dao.check(ids,status);
		if(result){ 
			String[] idarr = ids.split("[,]");
			for (String id : idarr) {
				PdaAmMeterList pdaAmMeterList = this.get(new Long(id.trim()));
				String lastMonth = DateUtil.getLastMonthStr(pdaAmMeterList.getMonth());
				Long lastDegree = getDegreeByTime(pdaAmMeterList.getAmId(), lastMonth);
				pdaAmMeterList.setLastDegree(lastDegree);
				boolean flag = crmService.pushAmRecord(pdaAmMeterList);
				if (flag) {
					pdaAmMeterList.setToCrmFlag(8);
				} else {
					pdaAmMeterList.setToCrmFlag(9);
					logger.warn("推送失败：" + pdaAmMeterList.getAmId() + " | " + pdaAmMeterList.getMonth());
				}
				//将抛转结果存起来，目前不做什么后续处理，仅记录。
				try {
					this.save(pdaAmMeterList);
				}  catch (Exception ex) {
					logger.error("保存失败", ex);
				}
			}
		}
		return result;
	}

	@Override
	public boolean saveFixedUsage(PdaAmMeterList pdaAmMeterList) {
		return dao.saveFixedUsage(pdaAmMeterList);
	}

    
}
